# This SQLGenerator is used for Liverpool Hospital databases only,
# And there is no realtime database in the Liverpool databases
# SQLGenerator will take all the user's inputs and return corresponding the SQL script
from DatabaseConnector import connection
import SyntaxParser
import RelationLoader

class SQLGenerator:
    def __new__(cls, *args, **kargs):
        if cls.instance is None:
            cls.instance = object.__new__(cls, *args, **kargs)
        return cls.instance   
    
#        SQLGenerator prototype (terminology_server_name, hospital_database_name, patient_demo, medical_criteria, diagnosis, duration, ward)       
#        terminology_server_name: SNOMED
#        hospital_database_name: hosrep
#        patient_demo: age > 20 and age < 50 and gender = male
#        medical_criteria: diagnosis code = c32
#        diagnosis: skin
#        time: 52 weeks
#        duration: 8736
#        ward: hosrep   
 
    def __init__(self, toc, db, answer, demo, medi, time_start, time_end , ward):
        self.toc = toc
        self.db = db
        self.answer_array = answer
        self.demo_array = demo
        self.med_array = medi
        self.time_start = time_start
        self.time_end = time_end
        self.ward = ward
        self.enabled_table = ["request_table","resultdetails_table", "patient_table"]
        self.current_table = []
        self.sqlscript = ""
        
        if self.demo_array is not None:
            for i in range(0, len(self.demo_array)):
                if self.current_table.count(self.demo_array[i].attribute.split(".")[0]) == 0:
                     self.current_table.append(self.demo_array[i].attribute.split(".")[0])
        if self.med_array is not None:
            for i in range(0, len(self.med_array)):
                if self.current_table.count(self.med_array[i].attribute.split(".")[0]) == 0:
                     self.current_table.append(self.med_array[i].attribute.split(".")[0])
#        print self.answer_array
        for i in range(0, len(self.answer_array)):
            if self.current_table.count(self.answer_array[i].split(".")[0]) == 0:
                 self.current_table.append(self.answer_array[i].split(".")[0])
    
    def Generator(self):
        if self.ValidateTable() <> True:
            return self.ValidateTable()
        
        loader = RelationLoader.RelationLoader()
        relation_array = []
        self.sqlscript = "SELECT "
#        print self.answer_array
        for i in range(0, len(self.answer_array)):
            self.sqlscript = self.sqlscript + self.answer_array[i] 
            if i < len(self.answer_array) -1:
                self.sqlscript = self.sqlscript + ", "
                
        self.sqlscript = self.sqlscript + " FROM "   
        for table1 in self.current_table:
            for table2 in self.current_table:
                if table1 < table2:
                    relation_array.append(table1 + "." + table2)
#        print relation_array
        
        cur_array = []
        for i in range(0, len(relation_array)):
            table1 = relation_array[i].split(".")[0]
            table2 = relation_array[i].split(".")[1]
            path_array = loader.FindRelatonPath(table1, table2)     
            if path_array == []:
                return "Table conflicts."
            else:
                for node in path_array:
                    if cur_array.count(node) == 0:
                        cur_array.append(node)
# Load the attributes list        
        for i in range(0, len(cur_array)):
            self.sqlscript = self.sqlscript + self.db + "." + cur_array[i]
            if i < len(cur_array) -1:
                self.sqlscript = self.sqlscript + ", "
        
        self.sqlscript = self.sqlscript + " where "
#        print cur_array

# Add table joint conditions, ie. patient_table.patientid = request_table.patientid
        for i in range(0, len(cur_array)-1):
            if loader.relation_array[0].count(cur_array[i]) <> 0:
                index = loader.relation_array[0].index(cur_array[i])
            else: 
                index = loader.relation_array[0].index(cur_array[i+1])
                    
            self.sqlscript = self.sqlscript + loader.relation_array[0][index] + "." + loader.relation_array[1][index] + " = " + loader.relation_array[2][index] + "." + loader.relation_array[3][index]
            if i < len(cur_array) -2:
                self.sqlscript = self.sqlscript + " and "

# Add medical conditions if any, ie. patient_table.sex = 'M'
        if self.med_array is not None:
            self.sqlscript = self.sqlscript + " and "
            for i in range(0, len(self.med_array)):
                if SyntaxParser.validateCondition(self.med_array[i].attribute) == "varchar":
                    self.sqlscript = self.sqlscript + self.med_array[i].attribute + " " + self.med_array[i].comparand + " '" + self.med_array[i].value.strip('"') + "'" + self.med_array[i].type
                elif SyntaxParser.validateCondition(self.med_array[i].attribute) == "numeric":
                     self.sqlscript = self.sqlscript + self.med_array[i].attribute + " " + self.med_array[i].comparand + " " + self.med_array[i].value + self.med_array[i].type
                elif SyntaxParser.validateCondition(self.med_array[i].attribute) == "unknown datatype":
                    return "Unknown medical datatype exists in where clause"
                    
# Add demographic conditions if any, ie. resultdetails_table.diagnosiscode = 'C32'                    
        if self.demo_array is not None:
            self.sqlscript = self.sqlscript + " and "
            for i in range(0, len(self.demo_array)):
                if SyntaxParser.validateCondition(self.demo_array[i].attribute) == "varchar":
                    self.sqlscript = self.sqlscript + self.demo_array[i].attribute + " " + self.demo_array[i].comparand + " '" + self.demo_array[i].value.strip("\"") + "'" + self.demo_array[i].type
                elif SyntaxParser.validateCondition(self.demo_array[i].attribute) == "numeric":
                     self.sqlscript = self.sqlscript + self.demo_array[i].attribute + " " + self.demo_array[i].comparand + " " + self.demo_array[i].value + self.demo_array[i].type
                elif SyntaxParser.validateCondition(self.demo_array[i].attribute) == "unknown datatype":
                    return "Unknown demographic datatype exists in where clause"

# Add date time conditions  
        self.sqlscript = self.sqlscript + " and request_table.datetime_request > '" + self.time_start + "' and request_table.datetime_request <= '" + self.time_end + "'"

# Add group by patientid conditions
#        self.sqlscript = self.sqlscript + " group by request_table.patientid"        

# Add order by date time conditions
        self.sqlscript = self.sqlscript + " order by request_table.datetime_request"     
    
        return self.sqlscript
    
    
    def ValidateTable(self):
        for table in self.current_table:
            if self.enabled_table.count(table) == 0:
                return "Query string contains invalid entities:%s" % table
        return True
                 
    
if __name__ == "__main__":
    toc = "SNOMED"
    db = "hosrep"
#    answer = "all values of mrn"
    patient = "age<50 and age>20 and gender = male"
    answer = ["request_table.patientid", "request_table.datetime_exam_received", "resultdetails_table.diagnosiscode", "resultdetails_table.examcode"]
#    cond = "diagnosis code = c32 or request id = 200 or datetime exam received > \"1998-01-01 00:00:00\""
    cond = ""
    diagnosis= ""
    duration= 8400
    ward= "hosrep"
    path = 'doc/interface/med_entities.txt'
    med_entities = SyntaxParser.buildHash(path)
    path = 'doc/interface/demo_entities.txt'
    demo_entities = SyntaxParser.buildHash(path)
    comparands = [">",">=","<","<=","=","<>"]
    types = ["and", "or"]
    patient_cond_array = SyntaxParser.getConditions(patient, comparands, types, demo_entities)
    cond_array = SyntaxParser.getConditions(cond, comparands, types, med_entities)
#    for i in range(0, len(patient_cond_array)):
#        print patient_cond_array[i].attribute
#    for i in range(0, len(cond_array)):
#        print cond_array[i].attribute
    s = SQLGenerator(toc, db, answer, patient_cond_array, cond_array, diagnosis, duration, ward)
    print s.Generator()
#    